/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.querie.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.sql.Driver;
import javax.naming.InitialContext;
import javax.sql.DataSource;



/**
 *
 * @author a-skoshy
 */
public class DBMSConnection {
	//Connection con = null;

	static int count = 0;
    /* Single method in the system to get a connection from the connection pool set up on the server
     *
     */
    public static Connection getConnection() throws SQLException {

        Connection con = null;
        if(con == null){
        	try {
                        InitialContext ctx = new InitialContext();
                        DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/querie");
                        con = ds.getConnection();

                     
        	} catch (Exception e) {
        		e.printStackTrace();
        		System.out.println("The number of connections opened =" + count);
        		throw new SQLException(e.getMessage());
        	}
        }
        return con;

      }
    /** closes a resultset, a statement and connection in the right order, and
     * only if they are not null.
     */
    public static synchronized void safeClose (ResultSet resultSet, Statement stmt, Connection connection) throws SQLException {

        //ResultSet
        if (resultSet != null) {
        	try {
            	resultSet.close();
            	resultSet = null;
            } catch (Exception e1) {
            	resultSet = null;
            }
        }

        //Statment Object
        if (stmt != null) {
        	try {
            	stmt.close();
            	stmt = null;
            } catch (Exception e2) {
            	stmt = null;
            }
        }

        //Connection Object
        if (connection != null) {
        	try {
            	connection.close();
            	connection = null;
            } catch (Exception e3) {
            	connection = null;
            }
        }

    }


    public static void main(String args[]){
    	String strSQL = "";
    	ResultSet rs0 = null;
    	Connection con = null;
    	Statement stmt = null;

     	try{
    		DBMSConnection dbcon = new DBMSConnection();
            con = dbcon.getConnection();
            stmt = con.createStatement();
            strSQL = "SELECT * FROM sessionqueries ";
            rs0 = stmt.executeQuery(strSQL);
            while(rs0.next()) {
            	int caseid = rs0.getInt(1);
            	System.out.println("Checking output caseid " + caseid);
            }
        }catch(SQLException s){
    		s.printStackTrace();

    	}finally{
    		try{
    			DBMSConnection.safeClose(rs0, stmt, con);
    		 }catch(SQLException s){
    	    		s.printStackTrace();

    	    }
    	}

    }
}
